﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Common.Tool.FileTools
{
    public class NpoiHelper
    {
        /// <summary>
        /// 写入单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        /// <param name="style"></param>
        public static void WriteCell(ISheet sheet, int row, int cell, string value, ICellStyle style = null)
        {
            try
            {
                if (sheet.GetRow(row) == null)
                    sheet.CreateRow(row);
                if (sheet.GetRow(row).GetCell(cell) == null)
                    sheet.GetRow(row).CreateCell(cell);
                ICell icell = sheet.GetRow(row).GetCell(cell);
                if (value == null) value = "";
                icell.SetCellValue(value.Trim());
                if (style != null)
                    icell.CellStyle = style;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 写入单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        /// <param name="style"></param>
        public static void WriteCell(IRow row, int cell, string value, ICellStyle style = null)
        {
            try
            {
                if (row.GetCell(cell) == null)
                    row.CreateCell(cell);
                ICell icell = row.GetCell(cell);
                if (value == null) value = "";
                icell.SetCellValue(value.Trim());
                if (style != null)
                    icell.CellStyle = style;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 写入单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        /// <param name="style"></param>
        public static void WriteCell(IRow row, int cell, int value, ICellStyle style = null)
        {
            try
            {
                if (row.GetCell(cell) == null)
                    row.CreateCell(cell);
                ICell icell = row.GetCell(cell);
                icell.SetCellValue(value);
                if (style != null)
                    icell.CellStyle = style;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string ReadCell(ISheet sheet, int row, int cell)
        {
            try
            {
                string value = null;
                if (sheet.GetRow(row) != null)
                {
                    if (sheet.GetRow(row).GetCell(cell) == null)
                        return value;
                    value = sheet.GetRow(row).GetCell(cell).ToString();
                }
                return value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static double ReadDoubleCell(ISheet sheet, int row, int cell)
        {
            try
            {
                double value = 0;
                if (sheet.GetRow(row) != null)
                {
                    if (sheet.GetRow(row).GetCell(cell) == null)
                        return value;
                    value = sheet.GetRow(row).GetCell(cell).NumericCellValue;
                }
                return value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 设置宽度
        /// </summary>
        /// <param name="sheet"></param>
        public static void SetAutoWidth(ISheet sheet)
        {
            try
            {
                //遍历单元格设置宽度
                for (int columnNum = 0; columnNum <= 10; columnNum++)
                {
                    int columnWidth = (int)(sheet.GetColumnWidth(columnNum) / 256);
                    for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow = sheet.GetRow(rowNum);
                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            string[] values = currentCell.ToString().Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                            foreach (var item in values)
                            {
                                //单元格宽度计算
                                int length = Encoding.Default.GetBytes(item).Length;
                                if (columnWidth < length)
                                {
                                    columnWidth = length;
                                }
                                if (columnWidth > 215)
                                {
                                    columnWidth = 215;
                                }
                            }
                        }
                    }
                    //设置列宽
                    sheet.SetColumnWidth(columnNum, columnWidth * 300);                   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取工作簿
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(string path)
        {
            try
            {
                IWorkbook workbook = null;
                using (FileStream fsread = File.OpenRead(path))
                {
                    if (path.Contains(".xlsx"))
                    {
                        workbook = new XSSFWorkbook(fsread);
                        return workbook;
                    }
                    if (path.Contains(".xls"))
                    {
                        workbook = new HSSFWorkbook(fsread);
                        return workbook;
                    }
                }
                return workbook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取工作簿
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(string name, byte[] data)
        {
            try
            {
                IWorkbook workbook = null;
                using (MemoryStream stream = new MemoryStream(data))
                {
                    if (name.Contains(".xlsx"))
                    {
                        workbook = new XSSFWorkbook(stream);
                        return workbook;
                    }
                    if (name.Contains(".xls"))
                    {
                        workbook = new HSSFWorkbook(stream);
                        return workbook;
                    }
                }
                return workbook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 保存表格
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="path"></param>
        public static void SaveExcel(IWorkbook workbook, string path)
        {
            try
            {
                using (FileStream fsread = File.OpenWrite(path))
                {
                    workbook.Write(fsread);
                    fsread.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
